﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using PromotionApp.DAL;
using PromotionApp.Model;
using System.Data;
using System.Text;
using PromotionApp.Utility;
using System.Transactions;
using System.Data.SqlClient;

namespace PromotionApp.BLL
{
    public class ReplyBLL
    {
        #region Private Variable
        
        private static ReplyBLL _Reply = null;

        #endregion

        #region Property
        #endregion

        #region Constructor

        private ReplyBLL()
        {            
        }
        
        #endregion

        #region Private Method

        private string GetDomainCondition(ref bool blnConditionFlag, string strDomainName)
        {
            string strCondition = string.Empty;

            if (strDomainName.Length > 0)
                strCondition = string.Format(" {0} c.[DomainName] = '{1}'", DynamicQueryHelper.GetConditionPrefix(ref blnConditionFlag), strDomainName);

            return strCondition;
        }

        private string GetCategoryCondition(ref bool blnConditionFlag, int categoryId)
        {
            string strCondition = string.Empty;

            if (categoryId > 0)
                strCondition = string.Format(" {0} b.[CategoryId] = '{1}'", DynamicQueryHelper.GetConditionPrefix(ref blnConditionFlag), categoryId);

            return strCondition;
        }   

        private string GetReplySummaryCTESql(string strDomainName, int categoryId, string strStartDate, string strEndDate)
        {
            StringBuilder sb = new StringBuilder();
            bool blnConditionFlag = false;

            sb.Append(" with temp as");
            sb.Append(" (");
            sb.Append(" select	row_number() over(order by a.[PostedDate] desc) as [No]");						
		    sb.Append(" , a.[ReplyId]");
		    sb.Append(" , a.[TopicId]");
		    sb.Append(" , a.[Message]");
		    sb.Append(" , isnull(a.[ImagePath], '') as [ImagePath] ");
		    sb.Append(" , isnull(a.[UserName], '') as [UserName]");
		    sb.Append(" , isnull(a.[Email], '') as [Email]");
		    sb.Append(" , a.[StatusCode]");
		    sb.Append(" , a.[PostedDate]");
            sb.Append(" from [dbo].[TxReply] as a");
	        sb.Append(" inner join [dbo].[TxTopic] as b on a.[TopicId] = b.[TopicId]");
	        sb.Append(" inner join [dbo].[MsCategory] as c on b.[CategoryId] = c.[CategoryId]");
            sb.Append(GetCategoryCondition(ref blnConditionFlag, categoryId));
            sb.Append(GetDomainCondition(ref blnConditionFlag, strDomainName));
            sb.AppendFormat(" {0} a.[PostedDate] between convert(datetime, '{1} 00:00:00', 103) and convert(datetime, '{2} 23:59:59', 103)", DynamicQueryHelper.GetConditionPrefix(ref blnConditionFlag), strStartDate, strEndDate);
            sb.Append(" )");
                       

            return sb.ToString();
        }

        private string GetReplySummarySearchSql(string strCTEQuery, int pageSize, int pageNumber)
        {
            StringBuilder sb = new StringBuilder();

            sb.Append(strCTEQuery);
            sb.Append(" select [No], [replyid], [TopicId], [Message], [ImagePath], [UserName], [Email], [StatusCode], [PostedDate]");
            sb.Append(" from temp");
            sb.AppendFormat(" where [No] between {0} and {1}", DynamicQueryHelper.GetStartNo(pageSize, pageNumber), DynamicQueryHelper.GetEndNo(pageSize, pageNumber));

            return sb.ToString();
        }

        private string GetReplySummaryCountSql(string strCTEQuery)
        {
            StringBuilder sb = new StringBuilder();

            sb.Append(strCTEQuery);
            sb.Append(" select count(*) as [RowCount]");
            sb.Append(" from temp");

            return sb.ToString();
        }

        #endregion

        #region Public Method

        public void DeleteReply(int replyId, int userId)
        {
            TransactionOptions option = new TransactionOptions();
            option.IsolationLevel = System.Transactions.IsolationLevel.Serializable;
            option.Timeout = TimeSpan.FromSeconds(30);

            ModulatorActivity log = new ModulatorActivity();
            log.PosteType = "R";
            log.PostId = replyId;
            log.StatusCode = "D";
            log.UserId = userId;

            using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew, option))
            {
                using (SqlConnection conn = TopicDAL.GetInstance().GetConnection())
                {
                    conn.Open();
                    ReplyDAL.GetInstance().UpdateRepleyStatus(replyId, "D", conn);
                    ModulatorActivityDAL.GetInstance().AddModulatorActivity(log, conn);
                    scope.Complete();
                }
            }           
        }

        public void PendingReply(int replyId, int userId)
        {
            TransactionOptions option = new TransactionOptions();
            option.IsolationLevel = System.Transactions.IsolationLevel.Serializable;
            option.Timeout = TimeSpan.FromSeconds(30);

            ModulatorActivity log = new ModulatorActivity();
            log.PosteType = "R";
            log.PostId = replyId;
            log.StatusCode = "P";
            log.UserId = userId;

            using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew, option))
            {
                using (SqlConnection conn = TopicDAL.GetInstance().GetConnection())
                {
                    conn.Open();
                    ReplyDAL.GetInstance().UpdateRepleyStatus(replyId, "P", conn);
                    ModulatorActivityDAL.GetInstance().AddModulatorActivity(log, conn);
                    scope.Complete();
                }
            }              
        }

        public void EnableReply(int replyId, int userId)
        {
            TransactionOptions option = new TransactionOptions();
            option.IsolationLevel = System.Transactions.IsolationLevel.Serializable;
            option.Timeout = TimeSpan.FromSeconds(30);

            ModulatorActivity log = new ModulatorActivity();
            log.PosteType = "R";
            log.PostId = replyId;
            log.StatusCode = "E";
            log.UserId = userId;

            using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew, option))
            {
                using (SqlConnection conn = TopicDAL.GetInstance().GetConnection())
                {
                    conn.Open();
                    ReplyDAL.GetInstance().UpdateRepleyStatus(replyId, "E", conn);
                    ModulatorActivityDAL.GetInstance().AddModulatorActivity(log, conn);
                    scope.Complete();
                }
            }              
        }

        public List<Reply> GetReplyById(int replyId)
        {
            List<Reply> list = new List<Reply>();
            DataSet ds = ReplyDAL.GetInstance().GetReplyById(replyId);

            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow r in ds.Tables[0].Rows)
                {
                    Reply o = new Reply();

                    o.ReplyId = Convert.ToInt32(r["replyid"]);
                    o.TopicId = Convert.ToInt32(r["TopicId"]);
                    o.Message = r["Message"].ToString().Trim();
                    o.ImagePath = r["ImagePath"].ToString().Trim();
                    o.StatusCode = r["StatusCode"].ToString().Trim();
                    o.UserName = r["UserName"].ToString().Trim();
                    o.Email = r["Email"].ToString().Trim();
                    o.PostedDate = Convert.ToDateTime(r["PostedDate"]);
                    list.Add(o);
                }
            }            

            return list;
        }

        public List<Reply> GetReplySummary(SearchPagingArgs e)
        {
            string strCTESql = GetReplySummaryCTESql(e.DomainName, e.CategoryId, e.StartDate, e.EndDate);
            string strSearchSql = GetReplySummarySearchSql(strCTESql, e.PageSize, e.PageNumber);
            string strCountQuery = GetReplySummaryCountSql(strCTESql);

            List<Reply> list = new List<Reply>();
            e.Total = ReplyDAL.GetInstance().GetTotalRow(strCountQuery);
            DynamicQueryHelper.GetMaxPageNumber(strCountQuery, e);
            DataSet ds = ReplyDAL.GetInstance().GetDataSetFromSql(strSearchSql);

            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow r in ds.Tables[0].Rows)
                {
                    Reply o = new Reply();

                    o.ReplyId = Convert.ToInt32(r["replyid"]);
                    o.TopicId = Convert.ToInt32(r["TopicId"]);                    
                    o.Message = r["Message"].ToString().Trim();
                    o.ImagePath = r["ImagePath"].ToString().Trim();
                    o.StatusCode = r["StatusCode"].ToString().Trim();
                    o.UserName = r["UserName"].ToString().Trim();
                    o.Email = r["Email"].ToString().Trim();
                    o.PostedDate = Convert.ToDateTime(r["PostedDate"]);
                    list.Add(o);
                }
            }            

            return list;
        }

        public List<Reply> GetRepliesByTopicIdAndStatus(int topicId, string strStatusCode)
        {
            List<Reply> list = new List<Reply>();
            DataSet ds = ReplyDAL.GetInstance().GetRepliesByTopicIdAndStatus(topicId, strStatusCode);

            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow r in ds.Tables[0].Rows)
                {
                    Reply o = new Reply();

                    o.ReplyId = Convert.ToInt32(r["ReplyId"]);
                    o.TopicId = Convert.ToInt32(r["TopicId"]);
                    o.Message = r["Message"].ToString().Trim();
                    o.ImagePath = r["ImagePath"].ToString().Trim();
                    o.UserName = r["UserName"].ToString().Trim();
                    o.Email = r["Email"].ToString().Trim();
                    o.StatusCode = r["StatusCode"].ToString().Trim();
                    o.PostedDate = Convert.ToDateTime(r["PostedDate"]);

                    list.Add(o);
                }
            }            

            return list;
        }

        public void AddReply(Reply reply, int categoryId)
        {
            TransactionOptions option = new TransactionOptions();
            option.IsolationLevel = System.Transactions.IsolationLevel.Serializable;
            option.Timeout = TimeSpan.FromSeconds(30);         

            using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew, option))
            {
                using (SqlConnection conn = TopicDAL.GetInstance().GetConnection())
                {
                    conn.Open();
                    ReplyDAL.GetInstance().AddReply(reply, conn);
                    CategoryLastedPostDAL.GetInstance().UpdateCategoryLastedPost(categoryId, conn);
                    scope.Complete();
                }
            }             
        }

        public static ReplyBLL GetInstance()
        {
            if (_Reply == null)
                _Reply = new ReplyBLL();

            return _Reply;
        }

        #endregion
    }
}